---
title: Create table
description: A create table operation creates a new table in the database.
---

## Structure

<YamlJsonTabs>
```yaml
create_table:
  name: name of new table
  columns: [...]
  constraints: [...]
```
```json
{
  "create_table": {
    "name": "name of new table",
    "columns": [...],
    "constraints": [...]
  }
}
```
</YamlJsonTabs>

Each `column` is defined as:

<YamlJsonTabs>
```yaml
- name: column name
  type: postgres type
  comment: postgres comment for the column
  nullable: true|false
  unique: true|false
  pk: true|false
  default: default value
  check:
    name: name of check constraint
    constraint: constraint expression
    no_inherit: true|false
  generated:
    expression: expression for stored column
    identity:
      user_specified_values: user specified values can be used, can be ALWAYS and BY DEFAULT. Default is ALWAYS
      sequence_options: sequence options for identity columns
  references:
    name: name of foreign key constraint
    table: name of referenced table
    column: name of referenced column
    on_delete: ON DELETE behaviour, can be CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION. Default is NO ACTION
    on_update: ON UPDATE behaviour, can be CASCADE, SET NULL, RESTRICT, or NO ACTION. Default is NO ACTION
    match_type: match type, can be SIMPLE or FULL. Default is SIMPLE
```
```json
{
  "name": "column name",
  "type": "postgres type",
  "comment": "postgres comment for the column",
  "nullable": true|false,
  "unique": true|false,
  "pk": true|false,
  "default": "default value",
  "check": {
    "name": "name of check constraint",
    "constraint": "constraint expression",
    "no_inherit": "true|false"
  },
  "generated": {
    "expression": "expression for stored column",
    "identity": {
      "user_specified_values": "user specified values can be used, can be ALWAYS and BY DEFAULT. Default is ALWAYS",
      "sequence_options": "sequence options for identity columns"
    }
  },
  "references": {
    "name": "name of foreign key constraint",
    "table": "name of referenced table",
    "column": "name of referenced column",
    "on_delete": "ON DELETE behaviour, can be CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION. Default is NO ACTION",
    "on_update": "ON UPDATE behaviour, can be CASCADE, SET NULL, RESTRICT, or NO ACTION. Default is NO ACTION",
    "match_type": "match type, can be SIMPLE or FULL. Default is SIMPLE"
  }
}
```
</YamlJsonTabs>

Each `constraint` is defined as:

<YamlJsonTabs>
```yaml
- name: constraint name
  type: constraint type
  columns: [list, of, columns]
  check: condition of CHECK constraint
  nulls_not_distinct: true|false
  deferrable: true|false
  initially_deferred: true|false
  no_inherit: true|false
  references:
    name: name of foreign key constraint
    table: name of referenced table
    columns: [list, of, referenced, columns]
    on_delete: ON DELETE behaviour, can be CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION. Default is NO ACTION
    on_delete_set_columns: [list of FKs to set, in on delete operation on SET NULL or SET DEFAULT]
    on_update: ON UPDATE behaviour, can be CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION. Default is NO ACTION
    match_type: match type, can be SIMPLE, FULL or PARTIAL. Default is SIMPLE
  index_parameters:
    tablespace: index_tablespace
    storage_parameters: parameter=value
    include_columns: [list, of, columns, included in index]
  exclude:
    index_method: name of the index method, e.g. btree
    elements: exclude elements
    predicate: WHERE clause of the exclude constraint
```
```json
{
  "name": "constraint name",
  "type": "constraint type",
  "columns": ["list", "of", "columns"],
  "check": "condition of CHECK constraint",
  "nulls_not_distinct": true|false,
  "deferrable": true|false,
  "initially_deferred": true|false,
  "no_inherit": true|false,
  "references": {
    "name": "name of foreign key constraint",
    "table": "name of referenced table",
    "columns": ["list", "of", "referenced", "columns"],
    "on_delete": "ON DELETE behaviour, can be CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION. Default is NO ACTION",
    "on_delete_set_columns": ["list of FKs to set", "in on delete operation on SET NULL or SET DEFAULT"],
    "on_update": "ON UPDATE behaviour, can be CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NO ACTION. Default is NO ACTION",
    "match_type": "match type, can be SIMPLE, FULL or PARTIAL. Default is SIMPLE"
  },
  "index_parameters": {
    "tablespace": "index_tablespace",
    "storage_parameters": "parameter=value",
    "include_columns": ["list", "of", "columns", "included in index"]
  },
  "exclude": {
    "index_method": "name of the index method, e.g. btree",
    "elements": "exclude elements",
    "predicate": "WHERE clause of the exclude constraint"
  }
}
```
</YamlJsonTabs>

Default values are subject to the usual rules for quoting SQL expressions. In particular, string literals should be surrounded with single quotes.

Generated columns can either be stored or identity columns. Options `generated.expression` and `generated.identity` cannot be set at the same time.

Supported constraint types: `unique`, `check`, `primary_key`, `foreign_key`, `exclude`.

Please note that you can only configure primary keys in `columns` list or `constraints` list, but
not in both places.

## Examples

### Create multiple tables

Create multiple tables. Each table is a separate operation in the migration:

<ExampleSnippet example="01_create_tables.yaml" languange="yaml" />

### Create one table

Create one table:

<ExampleSnippet example="02_create_another_table.yaml" languange="yaml" />

### Create one table (2)

Create one table with generated identity column:

<ExampleSnippet example="08_create_fruits_table.yaml" languange="yaml" />

### Create one table (3)

Create one table:

<ExampleSnippet example="20_create_posts_table.yaml" languange="yaml" />

### Create a table with a comment

Create a table with a comment on the table:

<ExampleSnippet example="12_create_employees_table.yaml" languange="yaml" />

### Create a table with nullable and non-nullable columns

Create a table with a mix of nullable and non-nullable columns:

<ExampleSnippet example="14_add_reviews_table.yaml" languange="yaml" />

### Create a table with a foreign key

Create a table with a foreign key constraint defined on a column:

<ExampleSnippet example="19_create_orders_table.yaml" languange="yaml" />

### Create a table with a `CHECK` constraint

Create a table with a `CHECK` constraint on one column:

<ExampleSnippet
  example="25_add_table_with_check_constraint.yaml"
  languange="yaml"
/>

### Create a table with column defaults

Create a table with different `DEFAULT` values:

<ExampleSnippet example="28_different_defaults.yaml" languange="yaml" />

### Create a table with multiple table level constraints

Create a table with table level constraints:

<ExampleSnippet example="50_create_table_with_table_constraint.yaml" languange="yaml" />

### Create a table with table level foreign key constraints

Create a table with table level foreign key constraints:

<ExampleSnippet example="51_create_table_with_table_foreign_key_constraint.yaml" languange="yaml" />

### Create a table with exclusion constraint

Create a table with an exclusion:

<ExampleSnippet example="52_create_table_with_exclusion_constraint.yaml" languange="yaml" />

### Create a table and set the version_schema field for the migration

<ExampleSnippet example="56_with_version_schema.yaml" languange="yaml" />
